package com.lam.common.utils.sql;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.lam.common.context.TokenUtils;
import com.lam.common.exception.BaseException;
import com.lam.common.utils.CollectionUtil;
import com.lam.common.utils.JsonUtil;
import com.lam.common.utils.RegexUtil;
import com.lam.common.utils.StringUtils;

/**
 * sql操作工具类
 * 
 * @author lam
 */
public class SqlUtil {
	
	private static final Logger logger = LoggerFactory.getLogger(SqlUtil.class);
	
	private final static String[] XSS_KEYWORD = "and |exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |;|or |+|user()".split("\\|");

	public static final String DATA_TYPE_SUFFIX_NUM = "_NUM";
	public static final String DATA_TYPE_SUFFIX_NUM_L = "_num";
	
	/**
	 * sql注入过滤处理，遇到注入关键字抛异常
	 * @param value
	 * @return
	 * @return
	 */
	public static String checkContent(String value) {

		if (StringUtils.isBlank(value)) {
			return value;
		}
		String ruleKeysValue = value.toLowerCase().replaceAll("/\\*.*\\*/", "");
		//value = value.toLowerCase().replaceAll("/\\*.*\\*/", "");

		if (CollectionUtil.contains(XSS_KEYWORD, ruleKeysValue)) {
			logger.error("检查到自定义SQL内容存在SQL注入关键词风险，  value：{}", value);
			throw new RuntimeException("检查到自定义SQL内容存在SQL注入关键词风险!---> " + value);
		}
		if (Pattern.matches("show\\s+tables", ruleKeysValue)) {
			throw new RuntimeException("检查到自定义SQL内容存在SQL注入关键词风险!--->" + value);
		}
		return value;
	}

	/**
	 * 检查字符，防止注入绕过
	 */
	public static String escapeOrderBySql(String value) {
		if (StringUtils.isNotBlank(value) && !isValidOrderBySql(value)) {
			throw new BaseException("参数不符合规范，不能进行查询");
		}
		return checkContent(value);
	}

	/**
	 * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
	 */
	public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";
	
	/**
	 * 验证 order by 语法是否符合规范
	 */
	public static boolean isValidOrderBySql(String value) {
		return value.matches(SQL_PATTERN);
	}
	
	/**
	 * 根据SQL模板提取变量
	 * 例：
	 * <pre>
	 *  模板：and project_id={project} and user_id={userId} => [project, userId]
	 * </pre>
	 * @param sqlTemplate
	 * @return
	 */
	public static List<String> getVarList(String sqlTemplate){
		return RegexUtil.getVarList(sqlTemplate);
	}
	
	/**
	 * 根据模板和变量值生成SQL片段
	 * 例：
	 * <pre>
	 *  模板：and project_id={projectId} and user_id={userId}, 变量值: ['23', 34]
	 *  <br>
	 *  生成SQL片段: and project_id='23' and user_id=34
	 * </pre>
	 * @param sqlTemplate 
	 * @param values 按照模板中的顺序传入变量
	 * @return
	 */
	public static String createSql(String sqlTemplate, Object[] values) {
		
    	if(StringUtils.isBlank(sqlTemplate)) {
    		return sqlTemplate;
    	}
    	List<String> varList = getVarList(sqlTemplate);
    	if(CollectionUtil.isEmpty(varList)) {
    		return sqlTemplate;
    	}
    	
    	return fillTemplate(sqlTemplate, varList, values);
    }
	
	/**
	 * 根据模板、变量名称和变量值填充SQL片段
	 * 例：
	 * <pre>
	 *  模板：and project_id={projectId} and user_id={userId} and age={age}, 变量名称:[projectId, userId, age], 变量值: ['23', 34, 20]
	 *  <br>
	 *  生成SQL片段: and project_id='23' and user_id=34 and age=20
	 * </pre>
	 * @param sqlTemplate
	 * @param varList 变量名称
	 * @param values 按照模板中的顺序传入变量
	 * @return
	 */
	public static String fillTemplate(String sqlTemplate, List<String> varList, Object[] values) {
		
		if(StringUtils.isBlank(sqlTemplate)) {
    		return sqlTemplate;
    	}
		
		if(CollectionUtil.isEmpty(varList)) {
			varList = getVarList(sqlTemplate);
		}
    	if(CollectionUtil.isEmpty(varList)) {
    		return sqlTemplate;
    	}
    	
    	sqlTemplate = sqlTemplate.toLowerCase();
    	int i = 0;
    	int valuesSize = values == null || values.length < 1 ? 0 : values.length;
    	for (String var : varList) {
    		var = var.trim().toLowerCase();
    		if("$uid".equalsIgnoreCase(var)) {
    			sqlTemplate = replace(sqlTemplate, var, TokenUtils.getLoginUserid());
    		}else if("$shopId".equalsIgnoreCase(var)) {
    			sqlTemplate = replace(sqlTemplate, var, TokenUtils.getLoginUserShopId());
    		}else{
    			if(i <= valuesSize) {
    				sqlTemplate = replace(sqlTemplate, var, values[i]);
    			}
    			i++;
    		}
		}
    	return sqlTemplate;
	}
	
	private static String replace(String sqlTemplate, String var, Object val) {
		if("$uid".equalsIgnoreCase(var) || "$shopId".equalsIgnoreCase(var) || var.endsWith(DATA_TYPE_SUFFIX_NUM_L)) {
			return sqlTemplate.replace(String.format("{%s}", var), val == null ? null :SqlUtil.checkContent(JsonUtil.toString(val)));
		}
		return sqlTemplate.replace(String.format("{%s}", var), String.format("'%s'", SqlUtil.checkContent(JsonUtil.toString(val))));
	}
	
	/**
	 * 解析SQL并获取表名
	 * @param sql
	 * @return
	 */
	public static List<String> getTableNameBySql(String sql) {
		
		List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
		if(CollectionUtil.isEmpty(stmtList)) {
			return Collections.emptyList();
		}
		
		List<String> tableList = new ArrayList<String>();
		MySqlSchemaStatVisitor visitor;
		for (SQLStatement sqlStatement : stmtList) {
			sqlStatement.accept(visitor = new MySqlSchemaStatVisitor());
			Map<TableStat.Name, TableStat> tables = visitor.getTables();
			for (TableStat.Name name : tables.keySet()) {
				if (StringUtils.isNotBlank(name.getName())) {
					tableList.add(name.getName());
				}
			}
		}
        return tableList;
    }
	
//	/**
//	 * 解析SQL并获取表名
//	 * @param sql
//	 * @return
//	 */
//	public static SQLExprTableSource getTableNameBySql(String sql) {
//        MySqlStatementParser mySqlStatementParser = new MySqlStatementParser(sql);
//        SQLStatement statement = mySqlStatementParser.parseStatement();
//        SQLExprTableSource sqlTableSource = null;
//        if(statement instanceof SQLSelectStatement){
//            SQLSelect selectQuery = ((SQLSelectStatement)statement).getSelect();
//            MySqlSelectQueryBlock sqlSelectQuery = (MySqlSelectQueryBlock)selectQuery.getQuery();
//            sqlTableSource = (SQLExprTableSource)sqlSelectQuery.getFrom();
//            // 可以获取where条件对象 SQLBinaryOpExpr
//            // selectList
//        }else if(statement instanceof SQLInsertStatement){
//            SQLInsertStatement sqlInsertStatement = (SQLInsertStatement)statement;
//            sqlTableSource = sqlInsertStatement.getTableSource();
//        }else if(statement instanceof SQLUpdateStatement){
//            SQLUpdateStatement sqlUpdateStatement = (SQLUpdateStatement)statement;
//            sqlTableSource = (SQLExprTableSource)sqlUpdateStatement.getTableSource();
//        }else if(statement instanceof SQLDeleteStatement){
//            SQLDeleteStatement sqlUpdateStatement = (SQLDeleteStatement)statement;
//            sqlTableSource = (SQLExprTableSource)sqlUpdateStatement.getTableSource();
//        }
//
//        return sqlTableSource;
//    }
}
